package com.ys.util.excel;

import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {
	
	public static void main(String[] args) throws IOException {
	    //读取线上文件
		//getRows("http://file.pos.shmtree.com/upload/2021/05/13/150748214.xls", 0);
        //读取本地文件
        FileInputStream fileInputStream = new FileInputStream(new File("D:/data/预约采购导入(1).xlsx"));
        List<List<String>> res = getRowsByInputStream(fileInputStream,0);
        System.out.println("res:\n"+res);
	}



    /**
     * 
     * @param urlStr
     * @param sheetNum 0代表第一个sheet
     */
    public static List<List<String>> getRows(String urlStr, Integer sheetNum) throws IOException {
    	
        URL url = new URL(urlStr);
        // 返回HttpURLConnection 对象
        URLConnection conn = url.openConnection();
        BufferedInputStream bis = null;
        bis = new BufferedInputStream(conn.getInputStream());

        //判断格式是否为excel文件
        //url.getPath得到文件的路径
        Workbook workbook = null;
        if (url.getPath().endsWith("xls")) {
            workbook = new HSSFWorkbook(bis);
        } else if (url.getPath().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(bis);
        } else {
            System.out.println("文件格式错误,请检查文件格式！");
        }
        //workbook.getNumberOfSheets() 总共多少页

        //第一页
        Sheet sheet = workbook.getSheetAt(sheetNum);

        List<List<String>> rows = new ArrayList<List<String>>();
        //总共有多少行
        int allRows = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < allRows; i++) {
            Row row = sheet.getRow(i);
            
            boolean flag = false;
            List<String> listStr = new ArrayList<String>();
            for (int j = 0; j < row.getLastCellNum(); j++) {

                Cell cell = row.getCell(j);
                listStr.add(cell != null ? cell.toString() : "");
                
                if(cell != null){
					flag = true;
				}
            }
            if (flag) {
                rows.add(listStr);
			}
        }
        workbook.close();
        bis.close();
        
        return rows;
    }

    /**
     * 
     * @param urlStr
     * @param sheetNum 0代表第一个sheet
     */
    public static List<List<String>> getRowsByInputStream(InputStream fileInputStream, Integer sheetNum) throws IOException {
    	
        BufferedInputStream bis = new BufferedInputStream(fileInputStream);

        //判断格式是否为excel文件
        //url.getPath得到文件的路径
        Workbook workbook = new XSSFWorkbook(bis);
        //workbook.getNumberOfSheets() 总共多少页

        //第一页
        Sheet sheet = workbook.getSheetAt(sheetNum);

        List<List<String>> rows = new ArrayList<List<String>>();
        //总共有多少行
        int allRows = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < allRows; i++) {
            Row row = sheet.getRow(i);
            
            boolean flag = false;
            List<String> listStr = new ArrayList<String>();
            for (int j = 0; row != null && j < row.getLastCellNum(); j++) {

                Cell cell = row.getCell(j);
                listStr.add(cell != null ? cell.toString() : "");
                
                if(cell != null){
					flag = true;
				}
            }
            if (flag) {
                rows.add(listStr);
			}
        }
        workbook.close();
        bis.close();
        
        return rows;
    }
    
    public static String replaceCell(String cell) {
    	if(cell != null){
    		cell = cell.replace("/", "").replace(":", "").replace(" ", "").replace("\t", "").replace("-", "");
		}
    	return cell;
    }
    
    /*
     * 转换成yyyyMMddHHmmss格式，如：20200101215953
     */
    public static String formatToYYYYMMDDHHmmss(String cell) {
    	if (cell == null) {
			return null;
		}
    	
    	cell = cell.trim();
    	String str2 = "";
    	for (int i = 0; i < cell.length(); i ++) {
    		if (cell.charAt(i) >= 48 && cell.charAt(i) <= 57) {
                str2 += cell.charAt(i);
            }
        }
    	
    	return str2;
    }


}
